package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.amado.easyui2.dao.CoachStaffDao;
import com.amado.easyui2.dao.impl.CoachCarDaoImpl.ShopRowMapper;
import com.amado.easyui2.domain.CoachCarEntity;
import com.amado.easyui2.domain.CoachStaffEntity;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

@Repository
public class CoachStaffDaoImpl implements CoachStaffDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	@Override
	public void add(CoachStaffEntity cn) {
		String sql = "insert into t_coach_staff(learn_card_number,driving_school_name,coach_name,coach_number,coach_id_card,coach_sex,coach_date_birth,nation,quasi_driving_type,must_teach_models,place_of_domicile,phone,reference_status,return_message,popedom,province,city,contact_address,must_teach_classes,learn_history,create_name)value(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(sql, cn.getLearnCardNumber(), cn.getDrivingSchoolName(), cn.getCoachName(),
				cn.getCoachNumber(), cn.getCoachIdCard(), cn.getCoachSex(), cn.getCoachDateBirth(), cn.getNation(),
				cn.getQuasiDrivingType(), cn.getMustTeachModels(), cn.getPlaceOfDomicile(), cn.getPhone(),
				cn.getReferenceStatus(), cn.getReturnMessage(), cn.getPopedom(), cn.getProvince(), cn.getCity(),
				cn.getContactAddress(), cn.getMustTeachClasses(), cn.getLearnHistory(), cn.getCreateName());
	}

	@Override
	public int delete(List<Long> ids) {
		StringBuilder sql = new StringBuilder("update t_coach_staff set delete_logo='Y' where id in (");
		for (int i = 0; i < ids.size(); i++) {
			if (i == 0) {
				sql.append("?");
			} else {
				sql.append(",?");
			}
		}
		sql.append(")");
		return getJdbcTemplate().update(sql.toString(), ids.toArray());

	}

	@Override
	public int update(CoachStaffEntity id) {
		String sql = "update t_coach_staff set coach_name=?,coach_number=?,coach_id_card=?,coach_sex=?,coach_date_birth=?,nation=?,quasi_driving_type=?,must_teach_models=?,place_of_domicile=?,phone=?,reference_status=?,return_message=?,popedom=?,province=?,city=?,contact_address=?,must_teach_classes=?,learn_history=?,update_time=?,update_name=?  where id=?";
		return getJdbcTemplate().update(sql, id.getCoachName(), id.getCoachNumber(), id.getCoachIdCard(),
				id.getCoachSex(), id.getCoachDateBirth(), id.getNation(), id.getQuasiDrivingType(),
				id.getMustTeachModels(), id.getPlaceOfDomicile(), id.getPhone(), id.getReferenceStatus(),
				id.getReturnMessage(), id.getPopedom(), id.getProvince(), id.getCity(), id.getContactAddress(),
				id.getMustTeachClasses(), id.getLearnHistory(), id.getUpdateTime(), id.getUpdateName(), id.getId());
	}

	@Override
	public CoachStaffEntity getCoach(Long id) {
		String sql = "select * from t_coach_staff where  id=?";
		return getJdbcTemplate().queryForObject(sql, new ShopRowMapper(), id);
	}

	@Override
	public List<CoachStaffEntity> listAll() {
		String sql = "select * from t_coach_staff where delete_logo='N'";
		return getJdbcTemplate().query(sql, new ShopRowMapper());
	}

	class ShopRowMapper implements RowMapper<CoachStaffEntity> {
		// rs为返回结果集，以每行为单位封装着
		@Override
		public CoachStaffEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
			CoachStaffEntity coachStaff = new CoachStaffEntity();
			coachStaff.setDrivingSchoolName(rs.getString("driving_school_name"));
			coachStaff.setCoachName(rs.getString("coach_name"));
			coachStaff.setCoachNumber(rs.getString("coach_number"));
			coachStaff.setCoachIdCard(rs.getString("coach_id_card"));
			coachStaff.setCoachSex(rs.getString("coach_sex"));
			coachStaff.setCoachDateBirth(rs.getString("coach_date_birth"));
			coachStaff.setNation(rs.getString("nation"));
			coachStaff.setQuasiDrivingType(rs.getString("quasi_driving_type"));
			coachStaff.setMustTeachModels(rs.getString("must_teach_models"));
			coachStaff.setPlaceOfDomicile(rs.getString("place_of_domicile"));
			coachStaff.setReferenceStatus(rs.getString("reference_status"));
			coachStaff.setReturnMessage(rs.getString("return_message"));
			coachStaff.setPopedom(rs.getString("popedom"));
			coachStaff.setProvince(rs.getString("province"));
			coachStaff.setCity(rs.getString("city"));
			coachStaff.setContactAddress(rs.getString("contact_address"));
			coachStaff.setMustTeachClasses(rs.getString("must_teach_classes"));
			coachStaff.setLearnHistory(rs.getString("learn_history"));
			coachStaff.setDrivingLicenceStatsTime(rs.getDate("driving_licence_stats_time"));
			coachStaff.setCertificateTime(rs.getDate("certificate_time"));
			coachStaff.setDeleteLogo(rs.getString("delete_logo"));
			coachStaff.setLearnCardNumber(rs.getString("learn_card_number"));
			coachStaff.setCreateTime(rs.getDate("create_time"));
			coachStaff.setUpdateTime(rs.getDate("update_time"));
			coachStaff.setCreateName(rs.getString("create_name"));
			coachStaff.setUpdateName(rs.getString("update_name"));
			return coachStaff;
		}
	}

	@Override
	public int listAllCount(String cn, String cIC) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM t_coach_staff where delete_logo='N'";
		if (cn != null && cn.trim() != "") {
			sql = sql + " and coach_name like ?";
		}
		if (cIC != null && cIC.trim() != "") {
			sql = sql + " and coach_id_card like ?";
		}
		System.out.println("sql" + sql);
		List<CoachStaffEntity> list = new ArrayList<CoachStaffEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (cIC != null && cIC.trim() != "") {
				ps.setString(j++, "%" + cIC + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public List<CoachStaffEntity> listAll(String cn, String cIC, Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM t_coach_staff where delete_logo='N'";
		if (cn != null && cn.trim() != "") {
			sql = sql + " and coach_name =?";
		}
		if (cIC != null && cIC.trim() != "") {
			sql = sql + " and coach_id_card like ?";
		}
		sql = sql + "limit ?,?";
		System.out.println("sql" + sql);
		List<CoachStaffEntity> list = new ArrayList<CoachStaffEntity>();
		try {
			ps = conn.prepareStatement(sql);
			int j = 1;
			if (cn != null && cn.trim() != "") {
				ps.setString(j++, cn);
			}
			if (cIC != null && cIC.trim() != "") {
				ps.setString(j++, "%" + cIC + "%");
			}
			ps.setInt(j++, (page.getCurrentPage() - 1) * page.getRowSize());
			ps.setInt(j++, page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				Long id = rs.getLong("id");
				String coachName = rs.getString("coach_name");
				String coachNumber = rs.getString("coach_number");
				String coachIdCard = rs.getString("coach_id_card");
				String coachSex = rs.getString("coach_sex");
				String learnHistory = rs.getString("learn_history");
				String placeOfDomicile = rs.getString("place_of_domicile");
				String nation = rs.getString("nation");
				String phone = rs.getString("phone");
				String popedom = rs.getString("popedom");
				String province = rs.getString("province");
				String city = rs.getString("city");
				String contactAddress = rs.getString("contact_address");
				Date createTime = rs.getTimestamp("create_time");
				Date updateTime = rs.getTimestamp("update_time");
				String createName = rs.getString("create_name");
				String updateName = rs.getString("update_name");

				CoachStaffEntity coachStaffEntity = new CoachStaffEntity();
				coachStaffEntity.setId(id);
				coachStaffEntity.setCoachName(coachName);
				coachStaffEntity.setCoachNumber(coachNumber);
				coachStaffEntity.setCoachIdCard(coachIdCard);
				coachStaffEntity.setLearnHistory(learnHistory);
				coachStaffEntity.setPlaceOfDomicile(placeOfDomicile);
				coachStaffEntity.setCoachSex(coachSex);
				coachStaffEntity.setNation(nation);
				coachStaffEntity.setPhone(phone);
				coachStaffEntity.setPopedom(popedom);
				coachStaffEntity.setProvince(province);
				coachStaffEntity.setCity(city);
				coachStaffEntity.setContactAddress(contactAddress);
				coachStaffEntity.setCreateTime(createTime);
				coachStaffEntity.setCreateName(createName);
				coachStaffEntity.setUpdateName(updateName);
				coachStaffEntity.setUpdateTime(updateTime);
				list.add(coachStaffEntity);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public CoachStaffEntity getByCodeName(String cn) {
		String sql = "select * from t_coach_staff where coach_name=?";
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new String[] { cn }, new ShopRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}

	}
}
